const { query, beginTransaction, commitTransaction, rollbackTransaction } = require('../config/db');

class Order {
  /**
   * 根据ID查找订单
   * @param {number} id - 订单ID
   * @returns {Promise<Object|null>} 订单信息
   */
  static async findById(id) {
    try {
      const orders = await query(`
        SELECT o.*, u.nickname as user_nickname, u.avatar_url as user_avatar
        FROM orders o 
        LEFT JOIN users u ON o.user_id = u.id 
        WHERE o.id = ?
      `, [id]);
      
      if (orders.length === 0) return null;
      
      const order = orders[0];
      // 解析JSON字段
      if (order.shipping_address) {
        try {
          // 检查是否已经是对象
          if (typeof order.shipping_address === 'object') {
            // 已经是对象，无需解析
          } else if (typeof order.shipping_address === 'string') {
            // 检查是否是无效的 "[object Object]" 字符串
            if (order.shipping_address === '[object Object]') {
              order.shipping_address = {};
            } else {
              order.shipping_address = JSON.parse(order.shipping_address);
            }
          } else {
            order.shipping_address = {};
          }
        } catch (error) {
          console.warn('解析 shipping_address JSON 失败:', order.shipping_address, error.message);
          order.shipping_address = {};
        }
      }
      if (order.contact_info) {
        try {
          // 检查是否已经是对象
          if (typeof order.contact_info === 'object') {
            // 已经是对象，无需解析
          } else if (typeof order.contact_info === 'string') {
            // 检查是否是无效的 "[object Object]" 字符串
            if (order.contact_info === '[object Object]') {
              order.contact_info = {};
            } else {
              order.contact_info = JSON.parse(order.contact_info);
            }
          } else {
            order.contact_info = {};
          }
        } catch (error) {
          console.warn('解析 contact_info JSON 失败:', order.contact_info, error.message);
          order.contact_info = {};
        }
      }
      
      // 获取订单商品
      const orderItems = await query(`
        SELECT oi.*, p.name as current_product_name, p.main_image_url as current_product_image
        FROM order_items oi
        LEFT JOIN products p ON oi.product_id = p.id
        WHERE oi.order_id = ?
      `, [id]);

      // 处理订单主表的数值字段类型转换
      if (order.total_amount !== null && order.total_amount !== undefined) {
        order.total_amount = parseFloat(order.total_amount);
      }
      if (order.shipping_fee !== null && order.shipping_fee !== undefined) {
        order.shipping_fee = parseFloat(order.shipping_fee);
      }
      if (order.payment_amount !== null && order.payment_amount !== undefined) {
        order.payment_amount = parseFloat(order.payment_amount);
      }

      // 处理订单商品的数值字段类型转换
      orderItems.forEach(item => {
        if (item.price !== null && item.price !== undefined) {
          item.price = parseFloat(item.price);
        }
        if (item.quantity !== null && item.quantity !== undefined) {
          item.quantity = parseInt(item.quantity);
        }
      });

      order.items = orderItems;

      return order;
    } catch (error) {
      console.error('查找订单失败:', error);
      throw error;
    }
  }

  /**
   * 根据订单号查找订单
   * @param {string} orderNumber - 订单号
   * @returns {Promise<Object|null>} 订单信息
   */
  static async findByOrderNumber(orderNumber) {
    try {
      const orders = await query(
        'SELECT * FROM orders WHERE order_number = ?',
        [orderNumber]
      );

      if (orders.length === 0) return null;

      const order = orders[0];

      // 处理数值字段类型转换
      if (order.total_amount !== null && order.total_amount !== undefined) {
        order.total_amount = parseFloat(order.total_amount);
      }
      if (order.shipping_fee !== null && order.shipping_fee !== undefined) {
        order.shipping_fee = parseFloat(order.shipping_fee);
      }
      if (order.payment_amount !== null && order.payment_amount !== undefined) {
        order.payment_amount = parseFloat(order.payment_amount);
      }

      return order;
    } catch (error) {
      console.error('查找订单失败:', error);
      throw error;
    }
  }

  /**
   * 创建订单
   * @param {Object} orderData - 订单数据
   * @returns {Promise<Object>} 创建的订单信息
   */
  static async create(orderData) {
    const connection = await beginTransaction();
    
    try {
      const {
        order_number, user_id, total_amount, shipping_fee, payment_amount,
        contact_info, shipping_address, customer_notes, items
      } = orderData;

      // 创建订单主记录
      const orderResult = await connection.execute(`
        INSERT INTO orders (
          order_number, user_id, total_amount, shipping_fee, payment_amount,
          shipping_address, contact_info, customer_notes, status
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'pending_payment')
      `, [
        order_number, user_id, total_amount, shipping_fee, payment_amount,
        JSON.stringify(shipping_address || {}), JSON.stringify(contact_info), customer_notes
      ]);

      const orderId = orderResult[0].insertId;

      // 创建订单商品记录
      for (const item of items) {
        await connection.execute(`
          INSERT INTO order_items (
            order_id, product_id, quantity, price, product_name, product_image_url
          ) VALUES (?, ?, ?, ?, ?, ?)
        `, [
          orderId, item.product_id, item.quantity, item.price,
          item.product_name, item.product_image_url
        ]);

        // 减少商品库存
        const stockResult = await connection.execute(
          'UPDATE products SET stock_quantity = stock_quantity - ? WHERE id = ? AND stock_quantity >= ?',
          [item.quantity, item.product_id, item.quantity]
        );

        if (stockResult[0].affectedRows === 0) {
          throw new Error(`商品库存不足: ${item.product_name}`);
        }
      }

      await commitTransaction(connection);
      return await this.findById(orderId);
    } catch (error) {
      await rollbackTransaction(connection);
      console.error('创建订单失败:', error);
      throw error;
    }
  }

  /**
   * 更新订单状态
   * @param {number} id - 订单ID
   * @param {string} status - 新状态
   * @param {Object} extraData - 额外数据
   * @returns {Promise<Object>} 更新后的订单信息
   */
  static async updateStatus(id, status, extraData = {}) {
    try {
      const fields = ['status = ?'];
      const values = [status];

      // 如果是支付状态，更新支付相关信息
      if (status === 'processing' && extraData.payment_time) {
        fields.push('payment_time = ?', 'transaction_id = ?', 'payment_method = ?');
        values.push(extraData.payment_time, extraData.transaction_id, extraData.payment_method);
      }

      values.push(id);
      await query(
        `UPDATE orders SET ${fields.join(', ')} WHERE id = ?`,
        values
      );

      return await this.findById(id);
    } catch (error) {
      console.error('更新订单状态失败:', error);
      throw error;
    }
  }

  /**
   * 获取订单列表（分页）
   * @param {Object} options - 查询选项
   * @returns {Promise<Object>} 订单列表和总数
   */
  static async getList(options = {}) {
    try {
      const {
        page = 1,
        pageSize = 10,
        search = '',
        status = null,
        user_id = null,
        start_date = null,
        end_date = null,
        sort = 'created_at',
        order = 'DESC'
      } = options;

      const offset = (page - 1) * pageSize;
      let whereClause = 'WHERE 1=1';
      let params = [];

      if (search) {
        whereClause += ' AND (o.order_number LIKE ? OR u.nickname LIKE ?)';
        params.push(`%${search}%`, `%${search}%`);
      }

      if (status) {
        whereClause += ' AND o.status = ?';
        params.push(status);
      }

      if (user_id) {
        whereClause += ' AND o.user_id = ?';
        params.push(user_id);
      }

      if (start_date) {
        whereClause += ' AND DATE(o.created_at) >= ?';
        params.push(start_date);
      }

      if (end_date) {
        whereClause += ' AND DATE(o.created_at) <= ?';
        params.push(end_date);
      }

      // 获取总数
      const countResult = await query(`
        SELECT COUNT(*) as total 
        FROM orders o 
        LEFT JOIN users u ON o.user_id = u.id 
        ${whereClause}
      `, params);
      const total = countResult[0].total;

      // 获取列表 - 使用字符串拼接避免 LIMIT 参数问题
      const orders = await query(`
        SELECT
          o.id, o.order_number, o.total_amount, o.payment_amount, o.status,
          o.created_at, o.payment_time, u.nickname as user_nickname
        FROM orders o
        LEFT JOIN users u ON o.user_id = u.id
        ${whereClause}
        ORDER BY o.${sort} ${order}
        LIMIT ${parseInt(pageSize)} OFFSET ${parseInt(offset)}
      `, params);

      // 处理订单列表的数值字段类型转换
      orders.forEach(order => {
        if (order.total_amount !== null && order.total_amount !== undefined) {
          order.total_amount = parseFloat(order.total_amount);
        }
        if (order.payment_amount !== null && order.payment_amount !== undefined) {
          order.payment_amount = parseFloat(order.payment_amount);
        }
      });

      return { orders, total };
    } catch (error) {
      console.error('获取订单列表失败:', error);
      throw error;
    }
  }

  /**
   * 获取待处理订单数量
   * @returns {Promise<Object>} 待处理订单统计
   */
  static async getPendingStats() {
    try {
      const result = await query(`
        SELECT 
          status,
          COUNT(*) as count
        FROM orders 
        WHERE status IN ('pending_payment', 'processing', 'shipped')
        GROUP BY status
      `);

      const stats = {
        pending_payment: 0,
        processing: 0,
        shipped: 0
      };

      result.forEach(item => {
        stats[item.status] = item.count;
      });

      return stats;
    } catch (error) {
      console.error('获取待处理订单统计失败:', error);
      throw error;
    }
  }

  /**
   * 获取订单统计数据
   * @returns {Promise<Object>} 统计数据
   */
  static async getStats() {
    try {
      // 订单总数
      const totalResult = await query('SELECT COUNT(*) as total FROM orders');
      const total = totalResult[0].total;

      // 今日订单数
      const todayResult = await query(
        'SELECT COUNT(*) as today FROM orders WHERE DATE(created_at) = CURDATE()'
      );
      const today = todayResult[0].today;

      // 昨日订单数
      const yesterdayResult = await query(
        'SELECT COUNT(*) as yesterday FROM orders WHERE DATE(created_at) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)'
      );
      const yesterday = yesterdayResult[0].yesterday;

      // 今日销售额
      const todaySalesResult = await query(
        'SELECT COALESCE(SUM(payment_amount), 0) as todaySales FROM orders WHERE DATE(payment_time) = CURDATE() AND status IN ("processing", "shipped", "completed")'
      );
      const todaySales = parseFloat(todaySalesResult[0].todaySales);

      // 昨日销售额
      const yesterdaySalesResult = await query(
        'SELECT COALESCE(SUM(payment_amount), 0) as yesterdaySales FROM orders WHERE DATE(payment_time) = DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND status IN ("processing", "shipped", "completed")'
      );
      const yesterdaySales = parseFloat(yesterdaySalesResult[0].yesterdaySales);

      // 累计销售额
      const totalSalesResult = await query(
        'SELECT COALESCE(SUM(payment_amount), 0) as totalSales FROM orders WHERE status IN ("processing", "shipped", "completed")'
      );
      const totalSales = parseFloat(totalSalesResult[0].totalSales);

      return {
        total,
        today,
        yesterday,
        todaySales,
        yesterdaySales,
        totalSales
      };
    } catch (error) {
      console.error('获取订单统计失败:', error);
      throw error;
    }
  }

  /**
   * 获取销售趋势（最近7天）
   * @returns {Promise<Array>} 趋势数据
   */
  static async getSalesTrend() {
    try {
      const result = await query(`
        SELECT 
          DATE(payment_time) as date,
          COUNT(*) as order_count,
          COALESCE(SUM(payment_amount), 0) as sales_amount
        FROM orders 
        WHERE payment_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
          AND status IN ('processing', 'shipped', 'completed')
        GROUP BY DATE(payment_time)
        ORDER BY date ASC
      `);

      return result.map(item => ({
        date: item.date,
        orderCount: item.order_count,
        salesAmount: parseFloat(item.sales_amount)
      }));
    } catch (error) {
      console.error('获取销售趋势失败:', error);
      throw error;
    }
  }
}

module.exports = Order;
